﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data.SqlClient;
using System.Configuration;
using System.Data;
using System.ComponentModel;
using System.Web.Security;

namespace TeamNitro
{
    public class DBManager
    {
        public String StudentID { get; set; }
        public String StaffID { get; set; }
        public String AdminID { get; set; }
        public String StudentName { get; set; }
        public String StaffName { get; set; }
        public String AdminName { get; set; }
        public String Password { get; set; }
        public int ContactNumber { get; set; }
        public String Address { get; set; }
        public String Email { get; set; }
        public String CurrentYear { get; set; }
        public String CurrentSem { get; set; }
        public String School { get; set; }
        public String CourseName { get; set; }
        public String CourseCode { get; set; }
        public String Position { get; set; }
        public String SubjectCode { get; set; }

        static string ConnectionString = "Data Source=localhost;Initial Catalog=subjectdb;Integrated Security = true";

        public static DBManager GetAdminInfo(String AdminID)
        {
            SqlConnection conn = null;
            DBManager result = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                conn.Open();

                comm.CommandText = "SELECT * FROM Admin WHERE AdminID = @AdminID";
                comm.Parameters.AddWithValue("@AdminID", AdminID);

                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    result = new DBManager();
                    result.AdminID = (String)dr["AdminID"];
                    result.AdminName = (String)dr["AdminName"];
                    result.Password = (String)dr["Password"];
                    result.ContactNumber = (int)dr["ContactNumber"];
                    result.Address = (String)dr["Address"];
                    result.Email = (String)dr["Email"];
                }
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static DataTable GetStudent()
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Student";
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static DataTable GetStaff()
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Staff";
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static DataTable GetAdmin()
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select * from Admin";
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static List<DBManager> GetUser(String type)
        {
            SqlConnection conn = null;
            List<DBManager> results = new List<DBManager>();

            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConnectionString;
                conn.Open();

                comm.Connection = conn;
                comm.CommandText = "select "+type+"ID from "+type;
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    DBManager b = new DBManager();

                    if (type == "Student")
                    {
                        b.StudentID = (String)dr["StudentID"];
                    }

                    else if (type == "Staff")
                    {
                        b.StaffID = (String)dr["StaffID"];
                    }

                    else if (type == "Admin")
                    {
                        b.AdminID = (String)dr["AdminID"];
                    }
                    results.Add(b);
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        public static List<DBManager> GetSubject(String type)
        {
            SqlConnection conn = null;
            List<DBManager> results = new List<DBManager>();

            try
            {
                conn = new SqlConnection();
                SqlCommand comm = new SqlCommand();
                conn.ConnectionString = ConnectionString;
                conn.Open();

                comm.Connection = conn;
                comm.CommandText = "select " + type + "Code from " + type+"Subject";
                SqlDataReader dr = comm.ExecuteReader();
                while (dr.Read())
                {
                    DBManager b = new DBManager();

                    if (type == "CDS")
                    {
                        b.SubjectCode = (String)dr["CDSCode"];
                    }

                    else if (type == "Elective")
                    {
                        b.SubjectCode = (String)dr["ElectiveCode"];
                    }

                    results.Add(b);
                }
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return results;
        }

        public static void UpdateStudent(DBManager b)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                if (b.Password == null)
                {
                    comm.CommandText = "Update Student Set StudentName=@StudentName, ContactNumber=@ContactNumber, Address=@Address, Email=@Email, CurrentYear=@CurrentYear, CurrentSem=@CurrentSem, School=@School Where StudentID=@StudentID";
                    comm.Parameters.AddWithValue("@StudentID", b.StudentID);
                    comm.Parameters.AddWithValue("@StudentName", b.StudentName);
                    comm.Parameters.AddWithValue("@ContactNumber", b.ContactNumber);
                    comm.Parameters.AddWithValue("@Address", b.Address);
                    comm.Parameters.AddWithValue("@Email", b.Email);
                    comm.Parameters.AddWithValue("@CurrentYear", b.CurrentYear);
                    comm.Parameters.AddWithValue("@CurrentSem", b.CurrentSem);
                    comm.Parameters.AddWithValue("@School", b.School);
                }

                else
                {
                    comm.CommandText = "Update Student Set StudentName=@StudentName, Password=@Password, ContactNumber=@ContactNumber, Address=@Address, Email=@Email, CurrentYear=@CurrentYear, CurrentSem=@CurrentSem, School=@School Where StudentID=@StudentID";
                    comm.Parameters.AddWithValue("@StudentID", b.StudentID);
                    comm.Parameters.AddWithValue("@StudentName", b.StudentName);
                    comm.Parameters.AddWithValue("@Password", FormsAuthentication.HashPasswordForStoringInConfigFile(b.Password, "sha1"));
                    comm.Parameters.AddWithValue("@ContactNumber", b.ContactNumber);
                    comm.Parameters.AddWithValue("@Address", b.Address);
                    comm.Parameters.AddWithValue("@Email", b.Email);
                    comm.Parameters.AddWithValue("@CurrentYear", b.CurrentYear);
                    comm.Parameters.AddWithValue("@CurrentSem", b.CurrentSem);
                    comm.Parameters.AddWithValue("@School", b.School);
                }
                
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }

        }

        public static void UpdateStaff(DBManager b)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;

                if (b.Password == null)
                {
                    comm.CommandText = "Update Staff Set StaffName=@StaffName, ContactNumber=@ContactNumber, Address=@Address, Email=@Email, Position=@Position, School=@School Where StaffID=@StaffID";
                    comm.Parameters.AddWithValue("@StaffID", b.StaffID);
                    comm.Parameters.AddWithValue("@StaffName", b.StaffName);
                    comm.Parameters.AddWithValue("@ContactNumber", b.ContactNumber);
                    comm.Parameters.AddWithValue("@Address", b.Address);
                    comm.Parameters.AddWithValue("@Email", b.Email);
                    comm.Parameters.AddWithValue("@Position", b.Position);
                    comm.Parameters.AddWithValue("@School", b.School);
                }

                else
                {
                    comm.CommandText = "Update Staff Set StaffName=@StaffName, Password=@Password, ContactNumber=@ContactNumber, Address=@Address, Email=@Email, Position=@Position, School=@School Where StaffID=@StaffID";
                    comm.Parameters.AddWithValue("@StaffID", b.StaffID);
                    comm.Parameters.AddWithValue("@StaffName", b.StaffName);
                    comm.Parameters.AddWithValue("@Password", FormsAuthentication.HashPasswordForStoringInConfigFile(b.Password, "sha1"));
                    comm.Parameters.AddWithValue("@ContactNumber", b.ContactNumber);
                    comm.Parameters.AddWithValue("@Address", b.Address);
                    comm.Parameters.AddWithValue("@Email", b.Email);
                    comm.Parameters.AddWithValue("@Position", b.Position);
                    comm.Parameters.AddWithValue("@School", b.School);
                }
                
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }

        }

        public static void UpdateAdmin(DBManager b)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;

                if (b.Password == null)
                {
                    comm.CommandText = "Update Admin Set AdminName=@AdminName, ContactNumber=@ContactNumber, Address=@Address, Email=@Email Where AdminID=@AdminID";
                    comm.Parameters.AddWithValue("@AdminID", b.AdminID);
                    comm.Parameters.AddWithValue("@AdminName", b.AdminName);
                    comm.Parameters.AddWithValue("@ContactNumber", b.ContactNumber);
                    comm.Parameters.AddWithValue("@Address", b.Address);
                    comm.Parameters.AddWithValue("@Email", b.Email);
                }

                else
                {
                    comm.CommandText = "Update Admin Set AdminName=@AdminName, Password=@Password, ContactNumber=@ContactNumber, Address=@Address, Email=@Email Where AdminID=@AdminID";
                    comm.Parameters.AddWithValue("@AdminID", b.AdminID);
                    comm.Parameters.AddWithValue("@AdminName", b.AdminName);
                    comm.Parameters.AddWithValue("@Password", FormsAuthentication.HashPasswordForStoringInConfigFile(b.Password, "sha1"));
                    comm.Parameters.AddWithValue("@ContactNumber", b.ContactNumber);
                    comm.Parameters.AddWithValue("@Address", b.Address);
                    comm.Parameters.AddWithValue("@Email", b.Email);
                }
                
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }

        }

        public static bool InsertStudent(DBManager b)
        {
            SqlConnection conn = null;
            bool result = false;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Student (StudentID, StudentName, Password, ContactNumber, Address, Email, CurrentYear, CurrentSem, School, CourseName, CourseCode) VALUES (@StudentID, @StudentName, @Password, @ContactNumber, @Address, @Email, @CurrentYear, @CurrentSem, @School, @CourseName, @CourseCode)";
                comm.Parameters.AddWithValue("@StudentID", b.StudentID);
                comm.Parameters.AddWithValue("@StudentName", b.StudentName);
                comm.Parameters.AddWithValue("@Password", b.Password);
                comm.Parameters.AddWithValue("@ContactNumber", b.ContactNumber);
                comm.Parameters.AddWithValue("@Address", b.Address);
                comm.Parameters.AddWithValue("@Email", b.Email);
                comm.Parameters.AddWithValue("@CurrentYear", b.CurrentYear);
                comm.Parameters.AddWithValue("@CurrentSem", b.CurrentSem);
                comm.Parameters.AddWithValue("@School", b.School);
                comm.Parameters.AddWithValue("@CourseName", b.CourseName);
                comm.Parameters.AddWithValue("@CourseCode", b.CourseCode);
                int rowsAffected = comm.ExecuteNonQuery();
                result = true;
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static bool InsertStaff(DBManager b)
        {
            SqlConnection conn = null;
            bool result = false;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Staff (StaffID, StaffName, Password, ContactNumber, Address, Email, Position, School) VALUES (@StaffID, @StaffName, @Password, @ContactNumber, @Address, @Email, @Position, @School)";
                comm.Parameters.AddWithValue("@StaffID", b.StaffID);
                comm.Parameters.AddWithValue("@StaffName", b.StaffName);
                comm.Parameters.AddWithValue("@Password", b.Password);
                comm.Parameters.AddWithValue("@ContactNumber", b.ContactNumber);
                comm.Parameters.AddWithValue("@Address", b.Address);
                comm.Parameters.AddWithValue("@Email", b.Email);
                comm.Parameters.AddWithValue("@Position", b.Position);
                comm.Parameters.AddWithValue("@School", b.School);
                int rowsAffected = comm.ExecuteNonQuery();
                result = true;
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static bool InsertAdmin(DBManager b)
        {
            SqlConnection conn = null;
            bool result = false;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Admin (AdminID, AdminName, Password, ContactNumber, Address, Email) VALUES (@AdminID, @AdminName, @Password, @ContactNumber, @Address, @Email)";
                comm.Parameters.AddWithValue("@AdminID", b.AdminID);
                comm.Parameters.AddWithValue("@AdminName", b.AdminName);
                comm.Parameters.AddWithValue("@Password", b.Password);
                comm.Parameters.AddWithValue("@ContactNumber", b.ContactNumber);
                comm.Parameters.AddWithValue("@Address", b.Address);
                comm.Parameters.AddWithValue("@Email", b.Email);
                int rowsAffected = comm.ExecuteNonQuery();
                result = true;
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return result;
        }

        public static void DeleteStudent(DBManager b)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Student WHERE StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID", b.StudentID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void DeleteStaff(DBManager b)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Staff WHERE StaffID=@StaffID";
                comm.Parameters.AddWithValue("@StaffID", b.StaffID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static void DeleteAdmin(DBManager b)
        {
            SqlConnection conn = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                SqlCommand comm = new SqlCommand();
                conn.Open();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Admin WHERE AdminID=@AdminID";
                comm.Parameters.AddWithValue("@AdminID", b.AdminID);
                int rowsAffected = comm.ExecuteNonQuery();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
        }

        public static DataTable Search(String id)
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select StudentID, StudentName, ContactNumber, Address, Email, CurrentYear, CurrentSem, School, CourseName, CourseCode from Student Where StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID", id);
                SqlDataReader dr = comm.ExecuteReader();
                DataTable dtStudent = new DataTable();
                dtStudent.Load(dr);
                dt = dtStudent;
                
                if (dt.Rows.Count == 0)
                {
                    comm.CommandText = "select StaffID, StaffName, ContactNumber, Address, Email, Position, School from Staff Where StaffID=@StaffID";
                    comm.Parameters.AddWithValue("@StaffID", id);
                    dr = comm.ExecuteReader();
                    DataTable dtStaff = new DataTable();
                    dtStaff.Load(dr);
                    dt = dtStaff;
                }
                
                if (dt.Rows.Count == 0)
                {
                    comm.CommandText = "select AdminID, AdminName, ContactNumber, Address, Email from Admin Where AdminID=@AdminID";
                    comm.Parameters.AddWithValue("@AdminID", id);
                    dr = comm.ExecuteReader();
                    DataTable dtAdmin = new DataTable();
                    dtAdmin.Load(dr);
                    dt = dtAdmin;
                }

                if (dt.Rows.Count == 0)
                {
                    comm.CommandText = "select * from CDSSubject Where CDSCode=@CDSCode";
                    comm.Parameters.AddWithValue("@CDSCode", id);
                    dr = comm.ExecuteReader();
                    DataTable dtCDS = new DataTable();
                    dtCDS.Load(dr);
                    dt = dtCDS;
                }

                if (dt.Rows.Count == 0)
                {
                    comm.CommandText = "select * from ElectiveSubject Where ElectiveCode=@ElectiveCode";
                    comm.Parameters.AddWithValue("@ElectiveCode", id);
                    dr = comm.ExecuteReader();
                    DataTable dtCDS = new DataTable();
                    dtCDS.Load(dr);
                    dt = dtCDS;
                }

                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static DataTable SearchCDSSubject(String id)
        {
            SqlConnection conn = null;
            DataTable dt = null;
            
            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select C.CDSCode,C.CDSName,CourseName,CourseCode from CDSSubject C,StudentCDSSubject S Where C.CDSCode=S.CDSCode And S.StudentID=@StudentID;";
                comm.Parameters.AddWithValue("@StudentID", id);
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static DataTable SearchElectiveSubject(String id)
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select E.ElectiveCode,E.ElectiveName,CourseName,CourseCode from ElectiveSubject E,StudentElectiveSubject S Where E.ElectiveCode=S.ElectiveCode And S.StudentID=@StudentID";
                comm.Parameters.AddWithValue("@StudentID", id);
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

        public static DataTable SearchUser(String type,String id)
        {
            SqlConnection conn = null;
            DataTable dt = null;

            try
            {
                conn = new SqlConnection();
                conn.ConnectionString = ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "select E.StudentID, StudentName, ContactNumber, Address, Email, CurrentYear, CurrentSem, School, CourseName, CourseCode from Student S,Student"+type+"Subject E Where E.StudentID=S.StudentID And E."+type+"Code=@SubjectCode;";
                comm.Parameters.AddWithValue("@SubjectCode", id);
                SqlDataReader dr = comm.ExecuteReader();
                dt = new DataTable();
                dt.Load(dr);
                dr.Close();
            }
            catch (SqlException e)
            {
                throw e;
            }
            finally
            {
                conn.Close();
            }
            return dt;
        }

    }
}